/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package rw.ac.kiosc.jdbc.school.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import rw.ac.kiosc.jdbc.school.dao.StudentDAO;
import rw.ac.kiosc.jdbc.school.orm.Department;
import rw.ac.kiosc.jdbc.school.orm.Student;

/**
 *
 * @author antoine
 */
public class StudentDAOImpl implements StudentDAO {

    private Connection connection;
    public static StudentDAOImpl instance;

    public static StudentDAOImpl getInstance(Connection connection) {
        if (instance == null) {
            instance = new StudentDAOImpl(connection);
        }
        return instance;
    }

    private StudentDAOImpl() {
    }

    private StudentDAOImpl(Connection connection) {
        this.connection = connection;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public synchronized Student saveStudent(Student student) {
        Student ret = null;
        try {
            Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.executeUpdate("INSERT INTO student(registration_no, name, department_id, year_of_study) values(\'" + student.getRegistrationNumber() + "\', \'" + student.getName() + "\', " + student.getDepartment().getDepartmentId() + ", " + student.getYearOfStudy() + ")");
            ResultSet studentsList = statement.executeQuery("SELECT * FROM student WHERE registration_no=\'" + student.getRegistrationNumber() + "\'");
            if (studentsList.next()) {
                ret = new Student(studentsList.getInt("student_id"),studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), student.getDepartment());
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public synchronized Student updateStudent(Student student) {
        Student ret = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statementOne.executeUpdate("UPDATE student SET registration_no=\'" + student.getRegistrationNumber() + "\', name=\'" + student.getName() + "\', department_id=" + student.getDepartment().getDepartmentId() + ", year_of_study=" + student.getYearOfStudy());
            ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE registration_no=\'" + student.getRegistrationNumber() + "\'");
            if (studentsList.next()) {
                ret = new Student(studentsList.getInt("student_id"),studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), student.getDepartment());
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public synchronized Student saveOrUpdateStudent(Student student) {
        if (student.getStudentId() > 0) {
            try {
                Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet studentsList = statement.executeQuery("SELECT student_id FROM student WHERE student_id=" + student.getStudentId());
                if (studentsList.next()) {
                    return updateStudent(student);
                } else {
                    return saveStudent(student);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
                return null;
            }
        } else {
            return saveStudent(student);
        }
    }

    public synchronized boolean deleteStudent(Student student) {
        try {
            Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            int n = statement.executeUpdate("DELETE FROM student WHERE student_id=" + student.getStudentId());
            return (n == 1) ? true : false;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public synchronized Student getStudentById(int studentId) {
        Student student = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet studentsList = statementOne.executeQuery("SELECT * FROM student WHERE student_id=" + studentId);
            if (studentsList.next()) {
                ResultSet departmentsList = statementTwo.executeQuery("SELECT * FROM department WHERE department_id=" + studentsList.getInt("department_id"));
                if (departmentsList.next()) {
                    Department dept = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                    student = new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), dept);
                    dept.getStudents().add(student);
                }
            }
            return student;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return student;
        }
    }

    public synchronized Student getStudentByRegistrationNumber(String registrationNumber) {
        Student student = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet studentsList = statementOne.executeQuery("SELECT * FROM student WHERE registration_no=\'" + registrationNumber + "\'");
            if (studentsList.next()) {
                ResultSet departmentsList = statementTwo.executeQuery("SELECT * FROM department WHERE department_id=" + studentsList.getInt("department_id"));
                if (departmentsList.next()) {
                    Department dept = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                    student = new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), dept);
                    dept.getStudents().add(student);
                }
            }
            return student;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return student;
        }
    }

    public synchronized List<Student> getStudentsByName(String name) {
        List<Student> students = new ArrayList<Student>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet studentsList = statementOne.executeQuery("SELECT * FROM student WHERE name LIKE \'" + name + "\'");
            while (studentsList.next()) {
                ResultSet departmentsList = statementTwo.executeQuery("SELECT * FROM department WHERE department_id=" + studentsList.getInt("department_id"));
                if (departmentsList.next()) {
                    Department dept = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                    Student retStd = new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), dept);
                    dept.getStudents().add(retStd);
                    students.add(retStd);
                }
            }
            return students;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return students;
        }
    }

    public synchronized List<Student> getStudentsByDepartment(Department department) {
        List<Student> students = new ArrayList<Student>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet studentsList = statementOne.executeQuery("SELECT * FROM student WHERE dept_id=" + department.getDepartmentId());
            while (studentsList.next()) {
                ResultSet departmentsList = statementTwo.executeQuery("SELECT * FROM department WHERE department_id=" + studentsList.getInt("department_id"));
                if (departmentsList.next()) {
                    Department dept = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                    Student retStd = new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), dept);
                    dept.getStudents().add(retStd);
                    students.add(retStd);
                }
            }
            return students;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return students;
        }
    }

    public synchronized List<Student> getStudentsByYearOfStudy(int yearOfStudy) {

        List<Student> students = new ArrayList<Student>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet studentsList = statementOne.executeQuery("SELECT * FROM student WHERE year_of_study=" + yearOfStudy);
            while (studentsList.next()) {
                ResultSet departmentsList = statementTwo.executeQuery("SELECT * FROM department WHERE department_id=" + studentsList.getInt("department_id"));
                if (departmentsList.next()) {
                    Department dept = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                    Student retStd = new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), dept);
                    dept.getStudents().add(retStd);
                    students.add(retStd);
                }
            }
            return students;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return students;
        }
    }

    public synchronized List<Student> getStudentsByYearOfStudyAndDepartment(int yearOfStudy, Department department) {

        List<Student> students = new ArrayList<Student>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet studentsList = statementOne.executeQuery("SELECT * FROM student WHERE year_of_study=" + yearOfStudy + " AND dept_id=" + department.getDepartmentId());
            while (studentsList.next()) {
                ResultSet departmentsList = statementTwo.executeQuery("SELECT * FROM department WHERE department_id=" + studentsList.getInt("department_id"));
                if (departmentsList.next()) {
                    Department dept = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                    Student retStd = new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), dept);
                    dept.getStudents().add(retStd);
                    students.add(retStd);
                }
            }
            return students;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return students;
        }
    }

    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<Student>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet studentsList = statementOne.executeQuery("SELECT * FROM student");
            while (studentsList.next()) {
                ResultSet departmentsList = statementTwo.executeQuery("SELECT * FROM department WHERE department_id=" + studentsList.getInt("department_id"));
                if (departmentsList.next()) {
                    Department dept = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                    Student student = new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), dept);
                    dept.getStudents().add(student);
                    students.add(student);
                }
            }
            return students;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return students;
        }
    }
}
